using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;

namespace Microsoft.SharedSource.SqlServer.MDDE.Components
{
    [
        System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1705:LongAcronymsShouldBePascalCased"),
        SupportedComponentType("{9CF90BF0-5BCC-4C63-B91D-1F322DC12C26}"),     
    ]
    class MDDEDataFlowDerivedColumnTransformation : MDDEDataFlowComponent
    {
        #region Member Variables

        private XmlNodeList changedProperties = null;
        private IDTSCustomPropertyCollection100 customProperties = null;
        XmlNodeList collectionInformation = null;
        bool preservePreviousCollections = true;

        //private Package originalPackage = new Package();
        IDTSComponentMetaData100 component = null;

        #endregion

        public MDDEDataFlowDerivedColumnTransformation(Package package, Object component, string name, string uri)
            : base(package, component, name, uri)
        {
        }

        #region Overridden Methods

        public override void GetChangeXml(MDDEComponent subject, XmlDocument changeXml)
        {
            #region Set Values

            if (subject == null)
                throw new ArgumentNullException("Subject can not NULL");

            if (!(subject is MDDEDataFlowDerivedColumnTransformation))
                throw new ArgumentException("Subject is not of type MDDEDataFlowDerivedColumnTransformation");

            component = (IDTSComponentMetaData100)innerComponent;
            IDTSComponentMetaData100 referenceComponent = (IDTSComponentMetaData100)subject.InnerComponent;

            IDTSCustomPropertyCollection100 componentPropertyCollection = (IDTSCustomPropertyCollection100)component.CustomPropertyCollection;
            IDTSCustomPropertyCollection100 referenceComponentPropertyCollection = (IDTSCustomPropertyCollection100)referenceComponent.CustomPropertyCollection;

            XmlNode componentNode = null;
            componentNode = changeXml.CreateElement("Component");

            #endregion

            #region Compare properties of Component with subject and create Properties' Xml

            for (int propertyIndex = 0; propertyIndex < componentPropertyCollection.Count; propertyIndex++)
            {
                if (componentPropertyCollection[propertyIndex].Name.CompareTo("Name") == 0)
                    continue;

                IDTSCustomProperty100 componentProperty = componentPropertyCollection[propertyIndex];
                IDTSCustomProperty100 referenceComponentProperty = referenceComponentPropertyCollection[propertyIndex];

                string componentPropertyValue = String.Empty;
                if (componentProperty.Value != null)
                    componentPropertyValue = componentProperty.Value.ToString();

                string referenceComponentPropertyValue = String.Empty;
                if (referenceComponentProperty.Value != null)
                    referenceComponentPropertyValue = referenceComponentProperty.Value.ToString();

                if (componentPropertyValue != referenceComponentPropertyValue)
                {
                    UpdateNodeProperties(changeXml, componentNode, componentProperty.Name, componentPropertyValue);
                }
            }

            #endregion

            #region Extract Custom Collections from Component

            UpdateNodeCollections(changeXml, componentNode, false);

            #region Get Input Collection Properties that differ from subject component

            foreach (IDTSCustomProperty100 componentProperty in component.InputCollection[0].CustomPropertyCollection)
            {
                foreach (IDTSCustomProperty100 referenceComponentProperty in referenceComponent.InputCollection[0].CustomPropertyCollection)
                {
                    if (referenceComponentProperty.Name == componentProperty.Name)
                    {
                        if (referenceComponentProperty.Value != componentProperty.Value)
                        {
                            string propertyValue = String.Empty;
                            if (componentProperty.Value != null)
                                propertyValue = componentProperty.Value.ToString();

                            UpdateNodeCollectionProperties(changeXml, componentNode, false,
                                component.InputCollection[0].Name, "Input",
                                componentProperty.Name, propertyValue);
                        }

                        break;
                    }
                }
            }

            #endregion

            #region Get Output Collection Properties that differ from subject component

            foreach (IDTSCustomProperty100 componentProperty in component.OutputCollection[0].CustomPropertyCollection)
            {
                foreach (IDTSCustomProperty100 referenceComponentProperty in referenceComponent.OutputCollection[0].CustomPropertyCollection)
                {
                    if (referenceComponentProperty.Name == componentProperty.Name)
                    {
                        if (referenceComponentProperty.Value != componentProperty.Value)
                        {
                            string propertyValue = String.Empty;
                            if (componentProperty.Value != null)
                                propertyValue = componentProperty.Value.ToString();

                            UpdateNodeCollectionProperties(changeXml, componentNode, false,
                                component.OutputCollection[0].Name, "Output",
                                componentProperty.Name, propertyValue);
                        }

                        break;
                    }
                }
            }

            #endregion

            #region Get Input Collection's Column Properties

            foreach (IDTSInputColumn100 inputColumn in component.InputCollection[0].InputColumnCollection)
            {
                foreach (IDTSCustomProperty100 columnProperty in inputColumn.CustomPropertyCollection)
                {
                    string columnPropertyValue = String.Empty;
                    if (columnProperty.Value != null)
                        columnPropertyValue = columnProperty.Value.ToString();

                    UpdateNodeCollectionColumnProperties(changeXml, componentNode, false,
                        component.InputCollection[0].Name, "Input", inputColumn.Name,
                        columnProperty.Name, columnPropertyValue);
                }
            }

            #endregion

            #region Get Output Collection's Column Properties

            foreach (IDTSOutputColumn100 outputColumn in component.OutputCollection[0].OutputColumnCollection)
            {
                foreach (IDTSCustomProperty100 columnProperty in outputColumn.CustomPropertyCollection)
                {
                    string columnPropertyValue = String.Empty;
                    if (columnProperty.Value != null)
                        columnPropertyValue = columnProperty.Value.ToString();

                    UpdateNodeCollectionColumnProperties(changeXml, componentNode, false,
                        component.OutputCollection[0].Name, "Output", outputColumn.Name,
                        columnProperty.Name, columnPropertyValue);
                }
            }

            #endregion

            #endregion

            #region Update Changes Xml

            AddAttribute(changeXml, componentNode, "Name", this.name);
            AddAttribute(changeXml, componentNode, "URL", this.uri);
            AddAttribute(changeXml, componentNode, "RowsetDescriptor", "");

            changeXml.FirstChild.AppendChild(componentNode);

            #endregion
        }

        public override bool ApplyChanges(XmlDocument changeXml, MDDEExecutionDAG originalPackage)
        {
            try
            {
                #region Set Values

                XmlNode componentChangeXml =
                    changeXml.SelectSingleNode("//PackageChanges/Component[@URL='" + this.uri + "']");

                //string packageXml = null;
                //package.SaveToXML(out packageXml, null);
                //originalPackage.LoadFromXML(packageXml, null);

                //if (innerComponent == null)
                //    return false;

                //if (!(innerComponent is IDTSComponentMetaData100))
                //    return false;

                component = (IDTSComponentMetaData100)innerComponent;

                #endregion

                #region Read Change Xml

                if (componentChangeXml != null)
                {
                    if (!ReadChangeXml(componentChangeXml))
                        return false;
                }

                #endregion

                #region Apply Properties and Verify If Sufficient Property Values' Are Available

                if (!ApplyProperties())
                    return false;

                #endregion

                #region Clear Invalid Input Columns

                component.RemoveInvalidInputColumns();

                #endregion

                #region Create Collections

                IDTSDesigntimeComponent100 designTimeComponent = component.Instantiate();

                #region Preserve Previous Collection Settings or Clear Collections based on User Preference

                component.InputCollection[0].InputColumnCollection.RemoveAll();
                component.OutputCollection[0].OutputColumnCollection.RemoveAll();

                if (preservePreviousCollections)
                {
                    IDTSComponentMetaData100 originalComponent =
                        (originalPackage.GetMDDEComponentByUri(uri)).InnerComponent as IDTSComponentMetaData100;
                    RestorePreviousCollections(designTimeComponent, originalComponent);
                }

                #endregion

                #region Override Collections with New Custom Collections

                if (collectionInformation != null)
                {
                    ApplyCustomCollections(designTimeComponent);
                }

                #endregion

                #endregion

                #region Reinitialize Component

                designTimeComponent.AcquireConnections(null);

                DTSValidationStatus validationStatus = designTimeComponent.Validate();

                if (validationStatus == DTSValidationStatus.VS_ISBROKEN)
                {
                    designTimeComponent.ReleaseConnections();
                    return false;
                }

                designTimeComponent.ReinitializeMetaData();
                designTimeComponent.ReleaseConnections();

                #endregion

                return true;
            }

            catch
            {
                // TODO: Add to Log
                return false;
            }
        }

        public override bool Validate()
        {
            try
            {
                IDTSDesigntimeComponent100 designTimeComponent = ((IDTSComponentMetaData100)innerComponent).Instantiate();

                designTimeComponent.AcquireConnections(null);
                DTSValidationStatus validationStatus = designTimeComponent.Validate();
                designTimeComponent.ReleaseConnections();

                if (validationStatus == DTSValidationStatus.VS_ISVALID)
                    return true;
                else
                    return false;
            }

            catch
            {
                // TODO: Add to Log
                return false;
            }
        }

        #endregion

        #region Helper Methods

        private bool ReadChangeXml(XmlNode changeXml)
        {
            if (changeXml.SelectSingleNode("Properties") != null)
                changedProperties = changeXml["Properties"].ChildNodes;

            if (component.CustomPropertyCollection == null)
                return false;

            customProperties = (IDTSCustomPropertyCollection100)component.CustomPropertyCollection;

            if (customProperties == null)
                return false;

            if (changeXml.SelectSingleNode("CollectionInformation") != null)
            {
                if (changeXml["CollectionInformation"].ChildNodes.Count > 0)
                    collectionInformation = changeXml["CollectionInformation"].ChildNodes;

                if (changeXml["CollectionInformation"].Attributes["PreservePreviousCollections"] != null)
                    preservePreviousCollections = Convert.ToBoolean(
                        changeXml["CollectionInformation"].Attributes["PreservePreviousCollections"].Value);
            }

            return true;
        }

        private bool ApplyProperties()
        {
            if (changedProperties != null)
            {
                foreach (XmlNode changedProperty in changedProperties)
                    customProperties[changedProperty.Attributes["Name"].Value].Value = this.ConvertToCompatibleType(changedProperty.Attributes["Value"].Value,
                        customProperties[changedProperty.Attributes["Name"].Value].Value.GetType().Name);
            }

            if (component.Instantiate().Validate()
                == DTSValidationStatus.VS_ISBROKEN)
                return false;
            else
                return true;
        }

        private List<string> ParseFriendlyExpression(string friendlyExpression)
        {
            List<string> columnNames = new List<string>();
            string columnName = String.Empty;

            for (int location = 0; location < friendlyExpression.Length; location++)
            {
                switch (friendlyExpression[location])
                {
                    case '\"':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            return null;
                        }

                        location = friendlyExpression.IndexOf('\"', location + 1);

                        if (location == -1)
                            location = friendlyExpression.Length;

                        break;

                    case '@':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            return null;
                        }

                        location = friendlyExpression.IndexOf('+', location);

                        if (location == -1)
                            location = friendlyExpression.Length;

                        break;

                    case '+':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            int result;
                            if (!Int32.TryParse(columnName.Trim(), out result))
                                columnNames.Add(columnName.Trim());

                            columnName = String.Empty;
                        }

                        break;

                    default:

                        columnName += friendlyExpression[location];

                        break;
                }
            }

            if (!String.IsNullOrEmpty(columnName.Trim()))
            {
                int result;
                if (!Int32.TryParse(columnName.Trim(), out result))
                    columnNames.Add(columnName.Trim());
            }

            if (columnNames.Count > 0)
                return columnNames;
            else
                return null;
        }

        private string CreateExpression(string friendlyExpression, IDTSInputColumnCollection100 inputColumnCollection)
        {
            string expression = String.Empty;
            string columnName = String.Empty;

            int startLocation;

            for (int location = 0; location < friendlyExpression.Length; location++)
            {
                switch (friendlyExpression[location])
                {
                    case '\"':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            return null;
                        }

                        startLocation = location;
                        location = friendlyExpression.IndexOf('\"', location + 1);

                        if (location == -1)
                            location = friendlyExpression.Length - 1;

                        expression += friendlyExpression.Substring(startLocation, location + 1 - startLocation);

                        break;

                    case '@':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            return null;
                        }

                        startLocation = location;
                        location = friendlyExpression.IndexOf('+', location);

                        if (location == -1)
                            location = friendlyExpression.Length;

                        expression += friendlyExpression.Substring(startLocation, location - startLocation);

                        break;

                    case '+':

                        if (!String.IsNullOrEmpty(columnName.Trim()))
                        {
                            int result;
                            if (!Int32.TryParse(columnName.Trim(), out result))
                            {
                                bool columnPresentInInput = false;
                                int columnLineageID = 0;
                                foreach (IDTSInputColumn100 inputColumn in inputColumnCollection)
                                {
                                    if (inputColumn.Name == columnName.Trim())
                                    {
                                        columnPresentInInput = true;
                                        columnLineageID = inputColumn.LineageID;
                                        break;
                                    }
                                }

                                if (columnPresentInInput)
                                    expression += "#" + columnLineageID;
                                else
                                    expression += columnName.Trim();
                            }
                            else
                                expression += result;
                        }

                        expression += "+";

                        columnName = String.Empty;

                        break;

                    default:

                        columnName += friendlyExpression[location];

                        break;
                }
            }

            if (!String.IsNullOrEmpty(columnName.Trim()))
            {
                int result;
                if (!Int32.TryParse(columnName.Trim(), out result))
                {
                    bool columnPresentInInput = false;
                    int columnLineageID = 0;
                    foreach (IDTSInputColumn100 inputColumn in inputColumnCollection)
                    {
                        if (inputColumn.Name == columnName.Trim())
                        {
                            columnPresentInInput = true;
                            columnLineageID = inputColumn.LineageID;
                            break;
                        }
                    }

                    if (columnPresentInInput)
                        expression += "#" + columnLineageID;
                    else
                        expression += columnName.Trim();
                }
                else
                    expression += result;
            }

            return expression;
        }

        private void ApplyCustomCollections(IDTSDesigntimeComponent100 designTimeComponent)
        {
            foreach (XmlNode collection in collectionInformation)
            {
                string collectionName = collection.Attributes["Name"].Value;

                #region If Collection is Input Collection

                if (collection.Attributes["Type"].Value == "Input")
                {
                    IDTSInput100 input = component.InputCollection[0];
                    IDTSOutput100 output = component.OutputCollection[0];
                    IDTSVirtualInput100 virutalInput = input.GetVirtualInput();

                    IDTSVirtualInputColumnCollection100 virtualInputColumnCollection
                        = virutalInput.VirtualInputColumnCollection;

                    if (collection.SelectSingleNode("Columns") != null)
                    {
                        XmlNodeList columns = collection.SelectSingleNode("Columns").ChildNodes;

                        if (columns != null)
                        {
                            foreach (XmlNode column in columns)
                            {
                                string columnName = column.Attributes["Name"].Value;

                                #region Check if Column is present in Input Collection

                                bool columnPresentInInput = false;

                                foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                {
                                    if (inputColumn.Name == columnName)
                                    {
                                        columnPresentInInput = true;
                                        break;
                                    }
                                }

                                #endregion

                                #region Check and Add if Column is present in Virtual Input Collection, if it is not present in Input Collection

                                bool columnPresentInVirtualInput = false;

                                if (!columnPresentInInput)
                                {
                                    foreach (IDTSVirtualInputColumn100 virtualInputColumn in virtualInputColumnCollection)
                                    {
                                        if (virtualInputColumn.Name == columnName)
                                        {
                                            columnPresentInVirtualInput = true;
                                            break;
                                        }
                                    }

                                    if (columnPresentInVirtualInput)
                                    {
                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                            virtualInputColumnCollection[columnName].LineageID, DTSUsageType.UT_READONLY);
                                    }
                                }

                                #endregion

                                #region Apply Properties

                                #region If Column was already present in Input Collection

                                if (columnPresentInInput)
                                {
                                    #region If Column do not contain FriendlyExpression Custom property (signified by UsageType as UT_READONLY)

                                    if (input.InputColumnCollection[columnName].UsageType == DTSUsageType.UT_READONLY)
                                    {
                                        if (column.SelectSingleNode("Properties") != null)
                                        {
                                            if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                            {
                                                foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                                {
                                                    if (columnProperty.Attributes["Name"].Value == "FriendlyExpression")
                                                    {
                                                        #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                                                        string friendlyExpression = columnProperty.Attributes["Value"].Value.ToString();

                                                        List<string> probableColumnList = ParseFriendlyExpression(friendlyExpression);

                                                        if (probableColumnList != null)
                                                        {
                                                            foreach (string probableColumn in probableColumnList)
                                                            {
                                                                #region Check if column is present in Virtual Input Collection

                                                                foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                                                {
                                                                    if (probableColumn == virtualInputColumnForProbable.Name)
                                                                    {
                                                                        #region Check if column is present in Input Collection

                                                                        columnPresentInInput = false;
                                                                        foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                        {
                                                                            if (probableColumn == inputColumnForProbable.Name)
                                                                            {
                                                                                columnPresentInInput = true;
                                                                                break;
                                                                            }
                                                                        }

                                                                        #endregion

                                                                        #region Add Column to Input if not present in Input Collection

                                                                        if (!columnPresentInInput)
                                                                        {
                                                                            designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                                virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                                                        }

                                                                        #endregion

                                                                        break;
                                                                    }
                                                                }

                                                                #endregion
                                                            }
                                                        }

                                                        #endregion

                                                        #region Add Expression and Friendly Expression for Column

                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                            virtualInputColumnCollection[columnName].LineageID, DTSUsageType.UT_READWRITE);

                                                        input.InputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value
                                                            = friendlyExpression;

                                                        string expression = CreateExpression(friendlyExpression, input.InputColumnCollection);

                                                        input.InputColumnCollection[columnName].CustomPropertyCollection["Expression"].Value = expression;

                                                        #endregion

                                                        #region Change the UsageType to UT_READWRITE (signifies that column has FriendlyExpression)

                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                            virtualInputColumnCollection[columnName].LineageID, DTSUsageType.UT_READWRITE);

                                                        #endregion

                                                        break;
                                                    }
                                                }
                                            }
                                        }
                                    }

                                    #endregion

                                    #region If Column contains FriendlyExpression Custom property (signified by UsageType as UT_READWRITE)

                                    else
                                    {
                                        if (column.SelectSingleNode("Properties") != null)
                                        {
                                            if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                            {
                                                foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                                {
                                                    if (columnProperty.Attributes["Name"].Value == "FriendlyExpression")
                                                    {
                                                        #region Remove Columns that Original Friendly Expression signifies

                                                        string originalFriendlyExpression
                                                            = input.InputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value.ToString();

                                                        List<string> probableColumnList = ParseFriendlyExpression(originalFriendlyExpression);

                                                        if (probableColumnList != null)
                                                        {
                                                            foreach (string probableColumn in probableColumnList)
                                                            {
                                                                #region Check if column is present in Input Collection

                                                                columnPresentInInput = false;
                                                                foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                {
                                                                    if (probableColumn == inputColumnForProbable.Name)
                                                                    {
                                                                        columnPresentInInput = true;
                                                                        break;
                                                                    }
                                                                }

                                                                #endregion

                                                                #region If column is present in Input Collection, remove if it is not referenced

                                                                if (columnPresentInInput)
                                                                {
                                                                    // UT_READWRITE signifies that column is atleast referenced in its FriendlyExpression
                                                                    if (input.InputColumnCollection[columnName].UsageType == DTSUsageType.UT_READONLY)
                                                                    {
                                                                        // If count is less than two, this implies that it is not referenced anywhere except this column's expresion
                                                                        int columnReferenced = 0;

                                                                        #region Check Input Columns' FriendlyExpressions and count probable FriendlyExpressions where it is referenced

                                                                        foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                                                        {
                                                                            if (inputColumn.UsageType == DTSUsageType.UT_READWRITE)
                                                                            {
                                                                                string referencedFriendlyExpression = inputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();
                                                                                List<string> referencedProbableColumnList = ParseFriendlyExpression(referencedFriendlyExpression);

                                                                                if (referencedProbableColumnList != null)
                                                                                {
                                                                                    foreach (string referencedProbableColumn in referencedProbableColumnList)
                                                                                    {
                                                                                        if (referencedProbableColumn == probableColumn)
                                                                                        {
                                                                                            columnReferenced += 1;
                                                                                            break;
                                                                                        }
                                                                                    }
                                                                                }
                                                                            }
                                                                        }

                                                                        #endregion

                                                                        #region Check Output Columns' FriendlyExpressions and count probable FriendlyExpressions where it is referenced

                                                                        foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                                                                        {
                                                                            string referencedFriendlyExpression = outputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();
                                                                            List<string> referencedProbableColumnList = ParseFriendlyExpression(referencedFriendlyExpression);

                                                                            if (referencedProbableColumnList != null)
                                                                            {
                                                                                foreach (string referencedProbableColumn in referencedProbableColumnList)
                                                                                {
                                                                                    if (referencedProbableColumn == probableColumn)
                                                                                    {
                                                                                        columnReferenced += 1;
                                                                                        break;
                                                                                    }
                                                                                }
                                                                            }
                                                                        }

                                                                        #endregion

                                                                        #region Check if column is referenced at more than one places

                                                                        if (columnReferenced < 2)
                                                                        {
                                                                            designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                                virtualInputColumnCollection[probableColumn].LineageID, DTSUsageType.UT_IGNORED);
                                                                        }

                                                                        #endregion
                                                                    }
                                                                }

                                                                #endregion
                                                            }
                                                        }

                                                        #endregion

                                                        #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                                                        string friendlyExpression = columnProperty.Attributes["Value"].Value.ToString();

                                                        probableColumnList = ParseFriendlyExpression(friendlyExpression);

                                                        if (probableColumnList != null)
                                                        {
                                                            foreach (string probableColumn in probableColumnList)
                                                            {
                                                                #region Check if column is present in Virtual Input Collection

                                                                foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                                                {
                                                                    if (probableColumn == virtualInputColumnForProbable.Name)
                                                                    {
                                                                        #region Check if column is present in Input Collection

                                                                        columnPresentInInput = false;
                                                                        foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                        {
                                                                            if (probableColumn == inputColumnForProbable.Name)
                                                                            {
                                                                                columnPresentInInput = true;
                                                                                break;
                                                                            }
                                                                        }

                                                                        #endregion

                                                                        #region Add Column to Input if not present in Input Collection

                                                                        if (!columnPresentInInput)
                                                                        {
                                                                            designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                                virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                                                        }

                                                                        #endregion

                                                                        break;
                                                                    }
                                                                }

                                                                #endregion
                                                            }
                                                        }

                                                        #endregion

                                                        #region Add Expression and Friendly Expression for Column

                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                            virtualInputColumnCollection[columnName].LineageID, DTSUsageType.UT_READWRITE);

                                                        input.InputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value
                                                            = friendlyExpression;

                                                        string expression = CreateExpression(friendlyExpression, input.InputColumnCollection);

                                                        input.InputColumnCollection[columnName].CustomPropertyCollection["Expression"].Value = expression;

                                                        #endregion
                                                    }
                                                }
                                            }
                                        }
                                    }

                                    #endregion
                                }

                                #endregion

                                #region If Column was not present in Input Collection but was present in Virtual Input Collection

                                else if (columnPresentInVirtualInput)
                                {
                                    if (column.SelectSingleNode("Properties") != null)
                                    {
                                        if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                        {
                                            foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                            {
                                                if (columnProperty.Attributes["Name"].Value == "FriendlyExpression")
                                                {
                                                    #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                                                    string friendlyExpression = columnProperty.Attributes["Value"].Value.ToString();

                                                    List<string> probableColumnList = ParseFriendlyExpression(friendlyExpression);

                                                    if (probableColumnList != null)
                                                    {
                                                        foreach (string probableColumn in probableColumnList)
                                                        {
                                                            #region Check if column is present in Virtual Input Collection

                                                            foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                                            {
                                                                if (probableColumn == virtualInputColumnForProbable.Name)
                                                                {
                                                                    #region Check if column is present in Input Collection

                                                                    columnPresentInInput = false;
                                                                    foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                    {
                                                                        if (probableColumn == inputColumnForProbable.Name)
                                                                        {
                                                                            columnPresentInInput = true;
                                                                            break;
                                                                        }
                                                                    }

                                                                    #endregion

                                                                    #region Add Column to Input if not present in Input Collection

                                                                    if (!columnPresentInInput)
                                                                    {
                                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                            virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                                                    }

                                                                    #endregion

                                                                    break;
                                                                }
                                                            }

                                                            #endregion
                                                        }
                                                    }

                                                    #endregion

                                                    #region Add Expression and Friendly Expression for Column

                                                    designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                        virtualInputColumnCollection[columnName].LineageID, DTSUsageType.UT_READWRITE);

                                                    input.InputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value
                                                        = friendlyExpression;

                                                    string expression = CreateExpression(friendlyExpression, input.InputColumnCollection);

                                                    input.InputColumnCollection[columnName].CustomPropertyCollection["Expression"].Value = expression;

                                                    #endregion

                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }

                                #endregion

                                #endregion
                            }
                        }
                    }
                }

                #endregion

                #region If Collection is Output Collection

                else
                {
                    IDTSInput100 input = component.InputCollection[0];
                    IDTSOutput100 output = component.OutputCollection[0];
                    IDTSVirtualInput100 virutalInput = input.GetVirtualInput();

                    IDTSVirtualInputColumnCollection100 virtualInputColumnCollection
                        = virutalInput.VirtualInputColumnCollection;

                    if (collection.SelectSingleNode("Columns") != null)
                    {
                        XmlNodeList columns = collection.SelectSingleNode("Columns").ChildNodes;

                        if (columns != null)
                        {
                            foreach (XmlNode column in columns)
                            {
                                string columnName = column.Attributes["Name"].Value;

                                #region Check if Column is present in Output Collection

                                bool columnPresentInOutput = false;

                                foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                                {
                                    if (outputColumn.Name == columnName)
                                    {
                                        columnPresentInOutput = true;
                                        break;
                                    }
                                }

                                #endregion

                                #region Add Column to Output Collection if not present

                                if (!columnPresentInOutput)
                                {
                                    IDTSOutputColumn100 newOutputColumn = output.OutputColumnCollection.New();
                                    newOutputColumn.Name = columnName;

                                    newOutputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR,
                                        51, 0, 0, 0);

                                    newOutputColumn.ErrorOrTruncationOperation = "Computation";
                                    newOutputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                                    newOutputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
                                }

                                #endregion

                                #region Apply Properties

                                #region If Column was already present in Output Collection

                                if (columnPresentInOutput)
                                {
                                    if (column.SelectSingleNode("Properties") != null)
                                    {
                                        if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                        {
                                            foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                            {
                                                if (columnProperty.Attributes["Name"].Value == "FriendlyExpression")
                                                {
                                                    #region Remove Columns that Original Friendly Expression signifies

                                                    string originalFriendlyExpression
                                                        = output.OutputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value.ToString();

                                                    List<string> probableColumnList = ParseFriendlyExpression(originalFriendlyExpression);

                                                    if (probableColumnList != null)
                                                    {
                                                        foreach (string probableColumn in probableColumnList)
                                                        {
                                                            #region Check if column is present in Input Collection

                                                            bool columnPresentInInput = false;
                                                            foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                            {
                                                                if (probableColumn == inputColumnForProbable.Name)
                                                                {
                                                                    columnPresentInInput = true;
                                                                    break;
                                                                }
                                                            }

                                                            #endregion

                                                            #region If column is present in Input Collection, remove if it is not referenced

                                                            if (columnPresentInInput)
                                                            {
                                                                // UT_READWRITE signifies that column is atleast referenced in its FriendlyExpression
                                                                // Mohit: columnName changed to probableColumn
                                                                if (input.InputColumnCollection[probableColumn].UsageType == DTSUsageType.UT_READONLY)
                                                                {
                                                                    // If count is less than two, this implies that it is not referenced anywhere except this column's expresion
                                                                    int columnReferenced = 0;

                                                                    #region Check Input Columns' FriendlyExpressions and count probable FriendlyExpressions where it is referenced

                                                                    foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                                                                    {
                                                                        if (inputColumn.UsageType == DTSUsageType.UT_READWRITE)
                                                                        {
                                                                            string referencedFriendlyExpression = inputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();
                                                                            List<string> referencedProbableColumnList = ParseFriendlyExpression(referencedFriendlyExpression);

                                                                            if (referencedProbableColumnList != null)
                                                                            {
                                                                                foreach (string referencedProbableColumn in referencedProbableColumnList)
                                                                                {
                                                                                    if (referencedProbableColumn == probableColumn)
                                                                                    {
                                                                                        columnReferenced += 1;
                                                                                        break;
                                                                                    }
                                                                                }
                                                                            }
                                                                        }
                                                                    }

                                                                    #endregion

                                                                    #region Check Output Columns' FriendlyExpressions and count probable FriendlyExpressions where it is referenced

                                                                    foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                                                                    {
                                                                        string referencedFriendlyExpression = outputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();
                                                                        List<string> referencedProbableColumnList = ParseFriendlyExpression(referencedFriendlyExpression);

                                                                        if (referencedProbableColumnList != null)
                                                                        {
                                                                            foreach (string referencedProbableColumn in referencedProbableColumnList)
                                                                            {
                                                                                if (referencedProbableColumn == probableColumn)
                                                                                {
                                                                                    columnReferenced += 1;
                                                                                    break;
                                                                                }
                                                                            }
                                                                        }
                                                                    }

                                                                    #endregion

                                                                    #region Check if column is referenced at more than one places

                                                                    if (columnReferenced < 2)
                                                                    {
                                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                            virtualInputColumnCollection[probableColumn].LineageID, DTSUsageType.UT_IGNORED);
                                                                    }

                                                                    #endregion
                                                                }
                                                            }

                                                            #endregion
                                                        }
                                                    }

                                                    #endregion

                                                    #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                                                    string friendlyExpression = columnProperty.Attributes["Value"].Value.ToString();

                                                    probableColumnList = ParseFriendlyExpression(friendlyExpression);

                                                    if (probableColumnList != null)
                                                    {
                                                        foreach (string probableColumn in probableColumnList)
                                                        {
                                                            #region Check if column is present in Virtual Input Collection

                                                            foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                                            {
                                                                if (probableColumn == virtualInputColumnForProbable.Name)
                                                                {
                                                                    #region Check if column is present in Input Collection

                                                                    bool columnPresentInInput = false;
                                                                    foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                    {
                                                                        if (probableColumn == inputColumnForProbable.Name)
                                                                        {
                                                                            columnPresentInInput = true;
                                                                            break;
                                                                        }
                                                                    }

                                                                    #endregion

                                                                    #region Add Column to Input if not present in Input Collection

                                                                    if (!columnPresentInInput)
                                                                    {
                                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                            virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                                                    }

                                                                    #endregion

                                                                    break;
                                                                }
                                                            }

                                                            #endregion
                                                        }
                                                    }

                                                    #endregion

                                                    #region Add Expression and Friendly Expression for Column

                                                    output.OutputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value
                                                        = friendlyExpression;

                                                    string expression = CreateExpression(friendlyExpression, input.InputColumnCollection);

                                                    output.OutputColumnCollection[columnName].CustomPropertyCollection["Expression"].Value = expression;

                                                    #endregion
                                                }
                                            }
                                        }
                                    }
                                }

                                #endregion

                                #region If Column was not present in Output Collection

                                else
                                {
                                    if (column.SelectSingleNode("Properties") != null)
                                    {
                                        if (column.SelectSingleNode("Properties").ChildNodes.Count > 0)
                                        {
                                            foreach (XmlNode columnProperty in column.SelectSingleNode("Properties").ChildNodes)
                                            {
                                                if (columnProperty.Attributes["Name"].Value == "FriendlyExpression")
                                                {
                                                    #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                                                    string friendlyExpression = columnProperty.Attributes["Value"].Value.ToString();

                                                    List<string> probableColumnList = ParseFriendlyExpression(friendlyExpression);

                                                    if (probableColumnList != null)
                                                    {
                                                        foreach (string probableColumn in probableColumnList)
                                                        {
                                                            #region Check if column is present in Virtual Input Collection

                                                            foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                                            {
                                                                if (probableColumn == virtualInputColumnForProbable.Name)
                                                                {
                                                                    #region Check if column is present in Input Collection

                                                                    bool columnPresentInInput = false;
                                                                    foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                                                    {
                                                                        if (probableColumn == inputColumnForProbable.Name)
                                                                        {
                                                                            columnPresentInInput = true;
                                                                            break;
                                                                        }
                                                                    }

                                                                    #endregion

                                                                    #region Add Column to Input if not present in Input Collection

                                                                    if (!columnPresentInInput)
                                                                    {
                                                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                                            virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                                                    }

                                                                    #endregion

                                                                    break;
                                                                }
                                                            }

                                                            #endregion
                                                        }
                                                    }

                                                    #endregion

                                                    #region Add Expression and Friendly Expression for Column

                                                    IDTSCustomProperty100 customProperty = output.OutputColumnCollection[columnName].CustomPropertyCollection.New();
                                                    customProperty.Name = "FriendlyExpression";

                                                    customProperty = output.OutputColumnCollection[columnName].CustomPropertyCollection.New();
                                                    customProperty.Name = "Expression";

                                                    output.OutputColumnCollection[columnName].CustomPropertyCollection["FriendlyExpression"].Value
                                                        = friendlyExpression;

                                                    string expression = CreateExpression(friendlyExpression, input.InputColumnCollection);

                                                    output.OutputColumnCollection[columnName].CustomPropertyCollection["Expression"].Value = expression;

                                                    #endregion

                                                    break;
                                                }
                                            }
                                        }
                                    }
                                }

                                #endregion

                                #endregion
                            }
                        }
                    }
                }

                #endregion
            }
        }

        private void RestorePreviousCollections(IDTSDesigntimeComponent100 designTimeComponent,
            IDTSComponentMetaData100 originalComponent)
        {
            //ObjectType objectType = ObjectType.Package;
            //IDTSComponentMetaData100 originalComponent
            //    = (IDTSComponentMetaData100)this.GetComponent(this.originalPackage, uri, ref objectType);

            IDTSInput100 originalInput = originalComponent.InputCollection[0];
            IDTSInput100 input = component.InputCollection[0];

            IDTSOutput100 originalOutput = originalComponent.OutputCollection[0];
            IDTSOutput100 output = component.OutputCollection[0];

            IDTSVirtualInput100 virutalInput = input.GetVirtualInput();

            IDTSVirtualInputColumnCollection100 virtualInputColumnCollection
                = virutalInput.VirtualInputColumnCollection;

            #region Add Columns to Input Collection based on Original Input Collection

            foreach (IDTSInputColumn100 originalInputColumn in originalInput.InputColumnCollection)
            {
                #region Check if Original Input Column is present in Virtual Input of New Component

                foreach (IDTSVirtualInputColumn100 virtualInputColumn in virtualInputColumnCollection)
                {
                    if (originalInputColumn.Name == virtualInputColumn.Name)
                    {
                        bool columnPresentInInput;

                        #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                        if (originalInputColumn.CustomPropertyCollection.Count > 0)
                        {
                            string friendlyExpression = originalInputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();

                            List<string> probableColumnList = ParseFriendlyExpression(friendlyExpression);

                            if (probableColumnList != null)
                            {
                                foreach (string probableColumn in probableColumnList)
                                {
                                    #region Check if column is present in Virtual Input Collection

                                    foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                                    {
                                        if (probableColumn == virtualInputColumnForProbable.Name)
                                        {
                                            #region Check if column is present in Input Collection

                                            columnPresentInInput = false;
                                            foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                            {
                                                if (probableColumn == inputColumnForProbable.Name)
                                                {
                                                    columnPresentInInput = true;
                                                    break;
                                                }
                                            }

                                            #endregion

                                            #region Add Column to Input if not present in Input Collection

                                            if (!columnPresentInInput)
                                            {
                                                designTimeComponent.SetUsageType(input.ID, virutalInput,
                                                    virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                            }

                                            #endregion

                                            break;
                                        }
                                    }

                                    #endregion
                                }
                            }
                        }

                        #endregion

                        #region Add Column to Input Collection

                        #region Check if column is present in Input Collection

                        columnPresentInInput = false;
                        foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
                        {
                            if (inputColumn.Name == virtualInputColumn.Name)
                            {
                                columnPresentInInput = true;
                                break;
                            }
                        }

                        #endregion

                        #region Add Column to Input if not present in Input Collection

                        if (!columnPresentInInput)
                        {
                            designTimeComponent.SetUsageType(input.ID, virutalInput,
                                virtualInputColumn.LineageID, DTSUsageType.UT_READONLY);
                        }

                        #endregion

                        #region Add Expression and Friendly Expression for Column

                        if (originalInputColumn.CustomPropertyCollection.Count > 0)
                        {
                            designTimeComponent.SetUsageType(input.ID, virutalInput,
                                virtualInputColumn.LineageID, DTSUsageType.UT_READWRITE);

                            //IDTSCustomProperty100 friendlyExpressionProperty =
                            //    input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection.New();
                            //friendlyExpressionProperty.Name = "FriendlyExpression";

                            input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["FriendlyExpression"].Value
                                = originalInputColumn.CustomPropertyCollection["FriendlyExpression"].Value;

                            string expression = CreateExpression(input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["FriendlyExpression"].Value.ToString(), input.InputColumnCollection);

                            //IDTSCustomProperty100 expressionProperty =
                            //    input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection.New();
                            //expressionProperty.Name = "Expression";

                            input.InputColumnCollection[virtualInputColumn.Name].CustomPropertyCollection["Expression"].Value = expression;
                        }

                        #endregion

                        #endregion

                        break;
                    }
                }

                #endregion
            }

            #endregion

            #region Add Columns to Output Collection based on Original Output Collection

            foreach (IDTSOutputColumn100 originalOutputColumn in originalOutput.OutputColumnCollection)
            {
                bool columnPresentInOutput;

                #region Verify Friendly Expression - Check Input Collection to verify if column in friendly expression is present or not

                if (originalOutputColumn.CustomPropertyCollection.Count > 0)
                {
                    string friendlyExpression = originalOutputColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString();

                    List<string> probableColumnList = ParseFriendlyExpression(friendlyExpression);

                    if (probableColumnList != null)
                    {
                        foreach (string probableColumn in probableColumnList)
                        {
                            #region Check if column is present in Virtual Input Collection

                            foreach (IDTSVirtualInputColumn100 virtualInputColumnForProbable in virtualInputColumnCollection)
                            {
                                if (probableColumn == virtualInputColumnForProbable.Name)
                                {
                                    #region Check if column is present in Input Collection

                                    columnPresentInOutput = false;
                                    foreach (IDTSInputColumn100 inputColumnForProbable in input.InputColumnCollection)
                                    {
                                        if (probableColumn == inputColumnForProbable.Name)
                                        {
                                            columnPresentInOutput = true;
                                            break;
                                        }
                                    }

                                    #endregion

                                    #region Add Column to Input if not present in Input Collection

                                    if (!columnPresentInOutput)
                                    {
                                        designTimeComponent.SetUsageType(input.ID, virutalInput,
                                            virtualInputColumnForProbable.LineageID, DTSUsageType.UT_READONLY);
                                    }

                                    #endregion

                                    break;
                                }
                            }

                            #endregion
                        }
                    }
                }

                #endregion

                #region Add Column to Output Collection

                #region Check if column is present in Output Collection

                columnPresentInOutput = false;
                foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
                {
                    if (outputColumn.Name == originalOutputColumn.Name)
                    {
                        columnPresentInOutput = true;
                        break;
                    }
                }

                #endregion

                #region Add Column to Output if not present in Output Collection

                if (!columnPresentInOutput)
                {
                    IDTSOutputColumn100 outputColumn = output.OutputColumnCollection.New();
                    outputColumn.Name = originalOutputColumn.Name;

                    outputColumn.SetDataTypeProperties(originalOutputColumn.DataType,
                        originalOutputColumn.Length, originalOutputColumn.Precision,
                        originalOutputColumn.Scale, originalOutputColumn.CodePage);

                    outputColumn.ErrorOrTruncationOperation = originalOutputColumn.ErrorOrTruncationOperation;
                    outputColumn.ErrorRowDisposition = originalOutputColumn.ErrorRowDisposition;
                    outputColumn.TruncationRowDisposition = originalOutputColumn.TruncationRowDisposition;
                }

                #endregion

                #region Add Expression and Friendly Expression for Column

                if (originalOutputColumn.CustomPropertyCollection.Count > 0)
                {
                    IDTSCustomProperty100 friendlyExpressionProperty =
                        output.OutputColumnCollection[originalOutputColumn.Name].CustomPropertyCollection.New();
                    friendlyExpressionProperty.Name = "FriendlyExpression";

                    friendlyExpressionProperty.Value
                        = originalOutputColumn.CustomPropertyCollection["FriendlyExpression"].Value;

                    string expression = CreateExpression(friendlyExpressionProperty.Value.ToString(), input.InputColumnCollection);

                    IDTSCustomProperty100 expressionProperty =
                        output.OutputColumnCollection[originalOutputColumn.Name].CustomPropertyCollection.New();
                    expressionProperty.Name = "Expression";

                    expressionProperty.Value = expression;
                }

                #endregion

                #endregion
            }

            #endregion
        }

        #endregion
    }
}
